package es.ats.codington.festival.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import es.ats.codington.festival.domain.Visitor;

/**
 * The Class VisitorDBQuery.
 *
 * @author 5Cs
 */
public class VisitorDBQuery {
	
	/**
	 * Execute register people.
	 *
	 * @param con the con
	 * @param visitor the visitor
	 * @param pStmt the stmt
	 * @return the int
	 */
	public int executeRegisterPeople (Connection con, Visitor visitor, PreparedStatement pStmt){
		int rows = 0;
		int key = 0;
		try{
			//Changing parameters to appropriate values
			pStmt.setString(1,visitor.getName());
			pStmt.setString(2,visitor.getLastName());
			pStmt.setInt(3,visitor.getAge());
			pStmt.setString(4,visitor.getAddress());
			pStmt.setString(5,visitor.getPhoneNumber());
			
			//Executing this query using executeUpdate()
			rows = pStmt.executeUpdate();
			
			//Committing
			con.commit();
			
			//Printing how many rows got added
			//System.out.println(rows + " row(s) in People table added!");
			
			//Get last id created in DB
			ResultSet keys = pStmt.getGeneratedKeys();
			keys.next();
			key = keys.getInt(1);
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		
		return key;
	}
	
	/**
	 * Execute register visitor.
	 *
	 * @param con the con
	 * @param visitor the visitor
	 * @param pStmt the stmt
	 * @param key the key
	 * @return the int
	 */
	public int executeRegisterVisitor (Connection con, Visitor visitor, PreparedStatement pStmt, int key){
		int rows = 0;
		try{
			//Changing parameters to appropriate values
			pStmt.setString(1,visitor.getUsername());
			pStmt.setString(2,visitor.getPassword());
			pStmt.setBoolean(3,visitor.isAdmin());
			pStmt.setInt(4,key);
			
			//Executing this query using executeUpdate()
			rows = pStmt.executeUpdate();
			
			//Committing
			con.commit();	
			
			//Printing how many rows got added
			//System.out.println(rows + " row(s) in Visitor table added!");			
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		
		return rows;
	}
	
	/**
	 * Execute delete people.
	 *
	 * @param con the con
	 * @param idVisitor the id visitor
	 * @param pStmt the stmt
	 * @return the int
	 */
	public int executeDeletePeople (Connection con, int idVisitor, PreparedStatement pStmt){
		int rows = 0;
		
		try{
			//Changing parameters to appropriate values
			pStmt.setInt(1,idVisitor);
			
			//Executing this query using executeUpdate()
			rows = pStmt.executeUpdate();
			
			//Committing
			con.commit();	

			//Printing how many rows got added
			//System.out.println(rows + " row(s) in People table deleted!");			
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return rows;
	}
	
	/**
	 * Execute delete visitor.
	 *
	 * @param con the con
	 * @param idVisitor the id visitor
	 * @param pStmt the stmt
	 */
	public void executeDeleteVisitor (Connection con, int idVisitor, PreparedStatement pStmt){
		int rows = 0;
		
		try{	    
			//Changing parameters to appropriate values
			pStmt.setInt(1,idVisitor);
		
			//Executing this query using executeUpdate()
			rows = pStmt.executeUpdate();
		
			//Committing
			con.commit();	

			//Printing how many rows got added
			//System.out.println(rows + " row(s) in Visitor table deleted!");
		}catch (Exception e){
			System.out.println(e.getMessage());
		}	
	}
	
	/**
	 * Execute update people.
	 *
	 * @param con the con
	 * @param visitor the visitor
	 * @param pStmt the stmt
	 */
	public void executeUpdatePeople (Connection con, Visitor visitor, PreparedStatement pStmt){
		int rows = 0;
		
		try{
			//Changing parameters to appropriate values
			pStmt.setString(1,visitor.getName());
			pStmt.setString(2,visitor.getLastName());
			pStmt.setInt(3,visitor.getAge());
			pStmt.setString(4,visitor.getAddress());
			pStmt.setString(5,visitor.getPhoneNumber());
			pStmt.setInt(6,visitor.getVisitorId());
			
			//Executing this query using executeUpdate()
			rows = pStmt.executeUpdate();
			
			//Committing
			con.commit();
			
			//Printing how many rows got added
			//System.out.println(rows + " row(s) in People table updated!");		
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
	}
	
	/**
	 * Execute update visitor.
	 *
	 * @param con the con
	 * @param visitor the visitor
	 * @param pStmt the stmt
	 * @return the int
	 */
	public int executeUpdateVisitor (Connection con, Visitor visitor, PreparedStatement pStmt){
		int rows = 0;
		
		try{
			//Changing parameters to appropriate values
			pStmt.setString(1,visitor.getUsername());
			pStmt.setString(2,visitor.getPassword());
			pStmt.setBoolean(3,visitor.isAdmin());
			pStmt.setInt(4,visitor.getVisitorId());
			
			//Executing this query using executeUpdate()
			rows = pStmt.executeUpdate();
			
			//Committing
			con.commit();	
			
			//Printing how many rows got added
			//System.out.println(rows + " row(s) in Visitor table updated!");	
			
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
		return rows;
	}
	
	/**
	 * Execute search people by id.
	 *
	 * @param con the con
	 * @param idVisitor the id visitor
	 * @param pStmt the stmt
	 * @param visitor the visitor
	 * @return the visitor
	 */
	public Visitor executeSearchPeopleById (Connection con, int idVisitor, PreparedStatement pStmt, Visitor visitor){
		int rows = 0;
		try{	    
			//Changing parameters to appropriate values
			pStmt.setInt(1,idVisitor);
		
			//Executing this query using executeUpdate()
			ResultSet resul = pStmt.executeQuery();
		
			//Committing
			con.commit();	
			
			while(resul.next()){	
				visitor.setName(resul.getString("Name"));
				visitor.setLastName(resul.getString("LastName"));
				visitor.setAge(resul.getInt("Age"));
				visitor.setAddress(resul.getString("Address"));
				visitor.setPhoneNumber(resul.getString("PhoneNumber"));
				rows++;
			}
		
			//Printing how many rows got added
			//System.out.println(rows + " row(s) in People table found!");
		}catch (Exception e){
			System.out.println(e.getMessage());
		}	
		return visitor;
	}
	
	/**
	 * Execute search visitor by id.
	 *
	 * @param con the con
	 * @param idVisitor the id visitor
	 * @param pStmt the stmt
	 * @return the visitor
	 */
	public Visitor executeSearchVisitorById (Connection con, int idVisitor, PreparedStatement pStmt){
		int rows = 0;
		Visitor visitor = new Visitor();
		try{	    
			//Changing parameters to appropriate values
			pStmt.setInt(1,idVisitor);
		
			//Executing this query using executeUpdate()
			ResultSet resul = pStmt.executeQuery();
		
			//Committing
			con.commit();	
			
			while(resul.next()){	
				visitor.setUsername(resul.getString("Username"));
				visitor.setPassword(resul.getString("Password"));
				visitor.setAdmin(resul.getBoolean("Admin"));
				visitor.setVisitorId(resul.getInt("VisitorId"));
				rows++;
			}
		
			//Printing how many rows got added
			//System.out.println(rows + " row(s) in Visitor table found!");
		}catch (Exception e){
			System.out.println(e.getMessage());
		}	
		return visitor;
	}

	/**
	 * Execute search visitor by username.
	 *
	 * @param con the con
	 * @param username the username
	 * @param pStmt the stmt
	 * @return the visitor
	 */
	public Visitor executeSearchVisitorByUsername (Connection con, String username, PreparedStatement pStmt){
		int rows = 0;
		Visitor visitor = new Visitor();
		try{	    
			//Changing parameters to appropriate values
			pStmt.setString(1,username);
		
			//Executing this query using executeUpdate()
			ResultSet resul = pStmt.executeQuery();
		
			//Committing
			con.commit();	
			
			while(resul.next()){	
				visitor.setUsername(resul.getString("Username"));
				visitor.setPassword(resul.getString("Password"));
				visitor.setAdmin(resul.getBoolean("Admin"));
				visitor.setVisitorId(resul.getInt("VisitorId"));
				rows++;
			}
		
			//Printing how many rows got added
			//System.out.println(rows + " row(s) in Visitor table found!");
		}catch (Exception e){
			System.out.println(e.getMessage());
		}	
		return visitor;
	}
	
	/**
	 * Execute search all people.
	 *
	 * @param con the con
	 * @param pStmt the stmt
	 * @param visitors the visitors
	 * @return the array list
	 */
	public ArrayList<Visitor> executeSearchAllPeople(Connection con, PreparedStatement pStmt, ArrayList<Visitor> visitors) {
		int i = 0;
		try{						
			//Executing this query using executeQuery()
			ResultSet rs = pStmt.executeQuery();
					
			//Committing
			con.commit();	
			
			while(rs.next())
			{
				visitors.get(i).setName(rs.getString("Name"));
				visitors.get(i).setLastName(rs.getString("LastName"));
				visitors.get(i).setAge(rs.getInt("Age"));
				visitors.get(i).setAddress(rs.getString("Address"));
				visitors.get(i).setPhoneNumber(rs.getString("PhoneNumber"));
				i++;
			}		
		}catch (Exception e){
			System.out.println(e.getMessage());
		}		
		return visitors;
	}
	
	/**
	 * Execute search all visitors.
	 *
	 * @param con the con
	 * @param pStmt the stmt
	 * @return the array list
	 */
	public ArrayList<Visitor> executeSearchAllVisitors(Connection con, PreparedStatement pStmt) {
		ArrayList<Visitor> visitors = new ArrayList<Visitor>();
		try{			
			//Executing this query using executeQuery()
			ResultSet rs = pStmt.executeQuery();
					
			//Committing
			con.commit();
			
			while(rs.next())
			{
				Visitor visitor = new Visitor();

				visitor.setUsername(rs.getString("Username"));
				visitor.setName(rs.getString("Password"));
				visitor.setLastName(rs.getString("Admin"));
				visitor.setVisitorId(rs.getInt("VisitorID"));

				visitors.add(visitor);
			}		
		}catch (Exception e){
			System.out.println(e.getMessage());
		}		
		return visitors;
	}
}
